import tkinter as tk
from tkinter import ttk, messagebox, simpledialog, filedialog
import sqlite3
from datetime import datetime
import pandas as pd

class EmployeeManagementSystem:
    def __init__(self, root):
        self.root = root
        self.root.title("员工信息管理系统2.0")
        self.root.geometry("1200x800")

        # 初始化数据库
        self.conn = sqlite3.connect('employee.db')
        self.init_database()

        # 创建界面组件
        self.create_widgets()
        self.load_employees()
        self.load_departments()

    def init_database(self):
        """初始化数据库表结构"""
        cursor = self.conn.cursor()

        cursor.execute('''
            CREATE TABLE IF NOT EXISTS departments (
                部门名称 TEXT PRIMARY KEY
            )
        ''')

        cursor.execute('''
            CREATE TABLE IF NOT EXISTS employees (
                员工ID INTEGER PRIMARY KEY AUTOINCREMENT,
                姓名 TEXT NOT NULL,
                职位 TEXT,
                部门 TEXT REFERENCES departments(部门名称),
                联系电话 TEXT,
                入职日期 TEXT NOT NULL,
                工资 REAL NOT NULL
            )
        ''')
        self.conn.commit()

    def create_widgets(self):
        """创建界面组件"""
        main_frame = ttk.Frame(self.root)
        main_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)

        # 左侧操作面板
        left_panel = ttk.Frame(main_frame, width=300)
        left_panel.pack(side=tk.LEFT, fill=tk.Y, padx=5)

        # 部门管理
        dept_frame = ttk.LabelFrame(left_panel, text="部门管理")
        dept_frame.pack(fill=tk.X, pady=5)

        ttk.Button(dept_frame, text="添加部门", command=self.add_department).pack(side=tk.LEFT, padx=2)
        ttk.Button(dept_frame, text="删除部门", command=self.delete_department).pack(side=tk.LEFT, padx=2)

        # 多条件搜索
        search_frame = ttk.LabelFrame(left_panel, text="高级搜索")
        search_frame.pack(fill=tk.X, pady=5)

        self.search_vars = {
            "姓名": tk.StringVar(),
            "职位": tk.StringVar(),
            "部门": tk.StringVar(),
            "联系电话": tk.StringVar()
        }

        row = 0
        for field in ["姓名", "职位", "部门",  "联系电话"]:
            ttk.Label(search_frame, text=field + ":").grid(row=row, column=0, padx=2, pady=2, sticky="e")
            entry = ttk.Entry(search_frame, textvariable=self.search_vars[field], width=18)
            entry.grid(row=row, column=1, padx=2, pady=2)
            row += 1

        ttk.Button(search_frame, text="搜索", command=self.advanced_search).grid(row=row, columnspan=2, pady=5)
        ttk.Button(search_frame, text="重置", command=self.reset_search).grid(row=row + 1, columnspan=2)

        # 操作按钮
        btn_frame = ttk.Frame(left_panel)
        btn_frame.pack(fill=tk.X, pady=10)

        ttk.Button(btn_frame, text="添加员工", command=self.add_employee).pack(fill=tk.X, pady=2)
        ttk.Button(btn_frame, text="编辑员工", command=self.edit_employee).pack(fill=tk.X, pady=2)
        ttk.Button(btn_frame, text="删除员工", command=self.delete_employee).pack(fill=tk.X, pady=2)
        ttk.Button(btn_frame, text="刷新数据", command=self.load_employees).pack(fill=tk.X, pady=2)
        ttk.Button(btn_frame, text="导出Excel", command=self.export_to_excel).pack(fill=tk.X, pady=2)
        ttk.Button(btn_frame, text="导入Excel", command=self.import_excel).pack(fill=tk.X, pady=2)
        ttk.Button(btn_frame, text="保存到数据库", command=self.save_imported_data).pack(fill=tk.X, pady=2)

        # 右侧数据显示
        right_panel = ttk.Frame(main_frame)
        right_panel.pack(side=tk.RIGHT, fill=tk.BOTH, expand=True)

        # 主员工列表
        main_tree_frame = ttk.LabelFrame(right_panel, text="员工列表")
        main_tree_frame.pack(fill=tk.BOTH, expand=True)

        columns = ("员工ID", "姓名", "职位", "部门", "联系电话", "入职日期", "工资")
        self.tree = ttk.Treeview(main_tree_frame, columns=columns, show="headings")

        col_widths = {
            "员工ID": 80,
            "姓名": 120,
            "职位": 150,
            "部门": 150,
            "联系电话": 120,
            "入职日期": 120,
            "工资": 100
        }

        for col in columns:
            self.tree.heading(col, text=col)
            self.tree.column(col, width=col_widths[col], anchor="center")

        self.tree.pack(fill=tk.BOTH, expand=True)

        # 搜索结果列表
        search_tree_frame = ttk.LabelFrame(right_panel, text="搜索结果/导入数据")
        search_tree_frame.pack(fill=tk.BOTH, expand=True)

        self.search_tree = ttk.Treeview(search_tree_frame, columns=columns, show="headings")
        for col in columns:
            self.search_tree.heading(col, text=col)
            self.search_tree.column(col, width=col_widths[col], anchor="center")
        self.search_tree.pack(fill=tk.BOTH, expand=True)

    def load_departments(self):
        """加载部门数据"""
        cursor = self.conn.cursor()
        cursor.execute("SELECT 部门名称 FROM departments")
        self.departments = [row[0] for row in cursor.fetchall()]

    def add_department(self):
        """添加部门"""
        dept = simpledialog.askstring("添加部门", "请输入部门名称:")
        if dept:
            try:
                cursor = self.conn.cursor()
                cursor.execute("INSERT INTO departments VALUES (?)", (dept,))
                self.conn.commit()
                self.load_departments()
                messagebox.showinfo("成功", f"部门 '{dept}' 已添加")
            except sqlite3.IntegrityError:
                messagebox.showerror("错误", "该部门已存在")

    def delete_department(self):
        """删除部门"""
        dept = simpledialog.askstring("删除部门", "请输入要删除的部门名称:")
        if dept in self.departments:
            if messagebox.askyesno("确认", f"确定要删除部门 '{dept}' 吗？"):
                try:
                    cursor = self.conn.cursor()
                    cursor.execute("DELETE FROM departments WHERE 部门名称=?", (dept,))
                    self.conn.commit()
                    self.load_departments()
                    messagebox.showinfo("成功", f"部门 '{dept}' 已删除")
                except sqlite3.IntegrityError:
                    messagebox.showerror("错误", "该部门下仍有员工，无法删除")
        else:
            messagebox.showerror("错误", "部门不存在")

    def load_employees(self):
        """加载员工数据"""
        for item in self.tree.get_children():
            self.tree.delete(item)

        cursor = self.conn.cursor()
        cursor.execute('SELECT * FROM employees')
        for row in cursor.fetchall():
            self.tree.insert("", "end", values=row)

    def add_employee(self):
        """添加员工"""
        self.employee_form("添加员工")

    def edit_employee(self):
        """编辑员工"""
        selected = self.tree.selection()
        if not selected:
            messagebox.showwarning("警告", "请先选择要编辑的员工")
            return
        self.employee_form("编辑员工", selected[0])

    def delete_employee(self):
        """删除员工"""
        selected = self.tree.selection()
        if not selected:
            messagebox.showwarning("警告", "请先选择要删除的员工")
            return

        if messagebox.askyesno("确认", "确定要删除该员工记录吗？"):
            emp_id = self.tree.item(selected[0], "values")[0]
            cursor = self.conn.cursor()
            cursor.execute("DELETE FROM employees WHERE 员工ID=?", (emp_id,))
            self.conn.commit()
            self.tree.delete(selected[0])

    def employee_form(self, title, item_id=None):
        """员工信息表单窗口"""
        form_window = tk.Toplevel(self.root)
        form_window.title(title)
        form_window.geometry("350x320")

        fields = ["姓名", "职位", "部门", "联系电话", "入职日期", "工资"]
        entries = {}

        for i, field in enumerate(fields):
            ttk.Label(form_window, text=field + ":").grid(row=i, column=0, padx=5, pady=2, sticky="e")

            if field == "部门":
                entry = ttk.Combobox(form_window, values=self.departments)
            else:
                entry = ttk.Entry(form_window)

            entry.grid(row=i, column=1, padx=5, pady=2)
            entries[field] = entry

        if item_id:
            values = self.tree.item(item_id, "values")
            entries["姓名"].insert(0, values[1])
            entries["职位"].insert(0, values[2])
            entries["部门"].set(values[3])
            entries["联系电话"].insert(0, values[4])
            entries["入职日期"].insert(0, values[5])
            entries["工资"].insert(0, values[6])

        ttk.Button(form_window, text="保存",
                   command=lambda: self.save_employee(form_window, entries, item_id)) \
            .grid(row=len(fields), columnspan=2, pady=10)

    def save_employee(self, window, entries, item_id):
        """保存员工信息"""
        data = {field: entry.get().strip() if not isinstance(entry, ttk.Combobox) else entry.get()
                for field, entry in entries.items()}

        required_fields = ["姓名", "部门", "入职日期", "工资"]
        for field in required_fields:
            if not data[field]:
                messagebox.showerror("错误", f"{field}不能为空")
                return

        try:
            datetime.strptime(data["入职日期"], "%Y-%m-%d")
            float(data["工资"])
            if data["联系电话"] and not data["联系电话"].isdigit():
                raise ValueError("联系电话格式不正确")
        except ValueError as e:
            messagebox.showerror("错误", str(e))
            return

        params = (
            data["姓名"],
            data["职位"],
            data["部门"],
            data["联系电话"],
            data["入职日期"],
            float(data["工资"])
        )

        cursor = self.conn.cursor()
        try:
            if item_id:
                emp_id = self.tree.item(item_id, "values")[0]
                cursor.execute('''
                    UPDATE employees SET 
                        姓名=?, 职位=?, 部门=?, 联系电话=?, 入职日期=?, 工资=?
                    WHERE 员工ID=?
                ''', params + (emp_id,))
            else:
                cursor.execute('''
                    INSERT INTO employees 
                    (姓名, 职位, 部门, 联系电话, 入职日期, 工资)
                    VALUES (?, ?, ?, ?, ?, ?)
                ''', params)

            self.conn.commit()
            self.load_employees()
            window.destroy()
        except sqlite3.IntegrityError as e:
            messagebox.showerror("错误", f"数据库错误: {str(e)}")

    def advanced_search(self):
        """高级搜索"""
        self.search_tree.delete(*self.search_tree.get_children())

        conditions = []
        params = []
        fields = {
            "姓名": self.search_vars["姓名"].get().strip(),
            "部门": self.search_vars["部门"].get().strip(),
            "职位": self.search_vars["职位"].get().strip(),
            "联系电话": self.search_vars["联系电话"].get().strip()
        }

        for field, value in fields.items():
            if value:
                conditions.append(f"{field} LIKE ?")
                params.append(f"%{value}%")

        cursor = self.conn.cursor()
        sql = "SELECT * FROM employees"
        if conditions:
            sql += " WHERE " + " AND ".join(conditions)

        cursor.execute(sql, params)
        for row in cursor.fetchall():
            self.search_tree.insert("", "end", values=row)

    def reset_search(self):
        """重置搜索条件"""
        for var in self.search_vars.values():
            var.set("")
        self.search_tree.delete(*self.search_tree.get_children())

    def export_to_excel(self):
        """导出到Excel"""
        try:
            df = pd.read_sql_query("SELECT * FROM employees", self.conn)
            df.to_excel("employees.xlsx", index=False)
            messagebox.showinfo("成功", "数据已导出到 employees.xlsx")
        except Exception as e:
            messagebox.showerror("错误", f"导出失败: {str(e)}")

    def import_excel(self):
        """导入Excel数据"""
        file_path = filedialog.askopenfilename(
            title="选择Excel文件",
            filetypes=[("Excel文件", "*.xlsx"), ("所有文件", "*.*")]
        )
        if not file_path:
            return

        try:
            df = pd.read_excel(file_path)
            required_columns = ['姓名', '职位', '部门', '联系电话', '入职日期', '工资']
            if not all(col in df.columns for col in required_columns):
                messagebox.showerror("错误", "Excel文件缺少必要列")
                return

            self.search_tree.delete(*self.search_tree.get_children())
            success_count = 0
            error_messages = []

            for index, row in df.iterrows():
                try:
                    # 数据清洗和验证
                    department = str(row['部门']).strip()
                    if department not in self.departments:
                        raise ValueError(f"部门'{department}'不存在")

                    hire_date = str(row['入职日期']).split()[0]  # 处理可能的时间格式
                    datetime.strptime(hire_date, "%Y-%m-%d")

                    salary = float(row['工资'])
                    phone = str(row['联系电话']).strip() if pd.notna(row['联系电话']) else ""
                    if phone and not phone.isdigit():
                        raise ValueError("联系电话格式错误")

                    self.search_tree.insert("", "end", values=(
                        "",  # 员工ID留空
                        row['姓名'],
                        row['职位'],
                        department,
                        phone,
                        hire_date,
                        salary
                    ))
                    success_count += 1
                except Exception as e:
                    error_messages.append(f"第{index + 2}行错误: {str(e)}")

            msg = f"成功导入 {success_count} 条记录"
            if error_messages:
                msg += f"，失败 {len(error_messages)} 条"
            messagebox.showinfo("导入结果", msg)
            if error_messages:
                messagebox.showerror("详细错误", "\n".join(error_messages[:10]))  # 最多显示前10个错误

        except Exception as e:
            messagebox.showerror("错误", f"导入失败: {str(e)}")

    def save_imported_data(self):
        """保存导入的数据到数据库"""
        items = self.search_tree.get_children()
        if not items:
            messagebox.showwarning("警告", "没有可保存的数据")
            return

        cursor = self.conn.cursor()
        success_count = 0
        error_messages = []

        try:
            for item in items:
                values = self.search_tree.item(item, 'values')
                try:
                    # 最终数据验证
                    department = values[3]
                    if department not in self.departments:
                        raise ValueError(f"部门'{department}'不存在")

                    datetime.strptime(values[5], "%Y-%m-%d")
                    float(values[6])
                    if values[4] and not values[4].isdigit():
                        raise ValueError("联系电话格式错误")

                    cursor.execute('''
                        INSERT INTO employees 
                        (姓名, 职位, 部门, 联系电话, 入职日期, 工资)
                        VALUES (?, ?, ?, ?, ?, ?)
                    ''', values[1:])  # 跳过ID字段
                    success_count += 1
                except Exception as e:
                    error_messages.append(f"员工{values[1]}保存失败: {str(e)}")

            self.conn.commit()
            self.load_employees()
            msg = f"成功保存 {success_count} 条记录"
            if error_messages:
                msg += f"，失败 {len(error_messages)} 条"
            messagebox.showinfo("保存结果", msg)
            if error_messages:
                messagebox.showerror("详细错误", "\n".join(error_messages[:10]))

        except Exception as e:
            self.conn.rollback()
            messagebox.showerror("错误", f"保存过程中发生错误: {str(e)}")

    def __del__(self):
        """关闭数据库连接"""
        self.conn.close()


if __name__ == "__main__":
    root = tk.Tk()
    app = EmployeeManagementSystem(root)
    root.mainloop()